package com.itcsystem.connection;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


public class ConnectionUtility {
	private Connection connection;
	private CallableStatement statement;
	private int indexInsert = 0;
	
	private static ConnectionUtility connectionUtility = new ConnectionUtility();
	
	private ConnectionUtility(){
		 try {
			 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
			 connection = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=ITCSystem","sa","password");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static ConnectionUtility getConnectionUtility(){
		return connectionUtility;
	}
	
	public void buildPreparedStatement(String sql, List<Object> values, boolean isInsert){
		try {
			statement = connection.prepareCall(sql);
			for(int index=0; index < values.size(); index++){
				statement.setObject(index + 1, values.get(index));
				if (isInsert && index == values.size()-1){
					statement.registerOutParameter(index + 2, java.sql.Types.INTEGER);
					indexInsert = index + 2;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void buildPreparedStatement(String sql, int valueId){
		try {
			statement = connection.prepareCall(sql);
			statement.setObject(1, valueId);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public Integer executeSQLInsert(){
		int result = 0;
		try{
			statement.executeUpdate();
			
			result = statement.getInt(indexInsert);
		}catch(SQLException ex){
			ex.printStackTrace();
		}finally{
		}
		
		return result;
	}
	
	public Boolean executeSQLDelete(){
		boolean result = false;
		try{
			result = (statement.executeUpdate() > 0); //El > 0 representa que se borraron registros
		}catch(SQLException ex){
			ex.printStackTrace();
		}finally{
		}
		
		return result;
	}
	
	public ResultSet getResult(String sql){
		ResultSet resultSet = null;
		
		try {
			statement = connection.prepareCall(sql);
			resultSet = statement.executeQuery();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return resultSet;
	}
}
